package mb.admin.dao;

import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.faces.model.SelectItem;
import javax.xml.xpath.XPathExpressionException;

import mb.commons.AbstractDAO;
import mb.commons.DbConnection;
import mb.commons.XmlHelper;
import mb.kurier.data.StatisticsData;

public class StatisticsDAO extends AbstractDAO {

	/**
	 * Konstruktor
	 */
	public StatisticsDAO() {
		conn = DbConnection.getConnection();
		helper = new XmlHelper("WEB-INF/xml/admin/statistics.xml");

	}
	
	/**
	 * Metoda pobiera z bazy danych listę kurierów
	 * 
	 * @return lista kurierów
	 */
	public ArrayList<SelectItem> getCourierList() {
		ArrayList<SelectItem> list = new ArrayList<SelectItem>();
		SelectItem data;
		try {
			query = helper.getSqlString("courierList","sqlString/prepareMain/sql");
			stmt = conn.createStatement();
			rs = stmt.executeQuery(query);
			while (rs.next()) {
				data = new SelectItem();
				data.setLabel(rs.getString("nazwisko") +" " +rs.getString("imie").charAt(0) +" ( id:"+rs.getInt("id_kuriera")+")");
				data.setValue(rs.getInt("id_kuriera"))	;
				list.add(data);
			}
			return list;
		} catch (SQLException e) {
			error(e.getErrorCode() + " : " + e.getMessage());
			e.printStackTrace();
		} catch (XPathExpressionException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}
		return null;
	}
	
	/**
	 * Metoda pobiera z bazy danych listę kurierów
	 * 
	 * @return lista kurierów
	 */
	public ArrayList<SelectItem> getDepartmentsList() {
		ArrayList<SelectItem> list = new ArrayList<SelectItem>();
		SelectItem data;
		try {
			query = helper.getSqlString("getIdOddzialuList","sqlString/prepareMain/sql");
			stmt = conn.createStatement();
			rs = stmt.executeQuery(query);
			while (rs.next()) {
				data = new SelectItem();
				data.setLabel(rs.getString("nazwa") +" ( id:"+rs.getInt("id")+")");
				data.setValue(rs.getInt("id"))	;
				list.add(data);
			}
			return list;
		} catch (SQLException e) {
			error(e.getErrorCode() + " : " + e.getMessage());
			e.printStackTrace();
		} catch (XPathExpressionException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}
		return null;
	}
	
	/**
	 * Metoda pobiera z dane do statystyk
	 * @param from odata od
	 * @param to data do
	 * @param param parametr zapytania sql
	 * @param typ typ wykresu
	 * @return lista obiektów statystyki
	 */
	public ArrayList<StatisticsData> getData( Date from , Date to ,int param,  String typ )
	{
		ArrayList<StatisticsData> lista = new ArrayList<StatisticsData>();
		try {
			if(typ.equals("1")) // wszystkie paczki w danej dacie
			{
				query = helper.getSqlString("wszystkiePaczki","sqlString/prepareMain/prepareStatment");
				
			}else if(typ.equals("2")) // paczki dostarczone 
			{
				query = helper.getSqlString("paczkiDostarczone","sqlString/prepareMain/prepareStatment");
				
			}else if(typ.equals("3")) // paczki nie dostarczone
			{
				query = helper.getSqlString("paczkiNiedostarczone","sqlString/prepareMain/prepareStatment");
				
			}else if(typ.equals("4"))// przejechane kilometry
			{
				query = helper.getSqlString("przebyteKilometry","sqlString/prepareMain/prepareStatment");
				
			}else if(typ.equals("5"))//oddziały - liczba paczek
			{
				query = helper.getSqlString("paczkiOddzialy","sqlString/prepareMain/prepareStatment");
				
			}else if(typ.equals("6"))//oddziały - liczba paczek
			{
				query = helper.getSqlString("zyski","sqlString/prepareMain/prepareStatment");
				
			}
			
			prepStmt = conn.prepareStatement(query);
			if(!typ.equals("6"))
			{
				prepStmt.setInt(1, param);
				prepStmt.setDate(2, from);
				prepStmt.setDate(3, to);
			}else
			{
				prepStmt.setDate(1, from);
				prepStmt.setDate(2, to);
			}
			
			ResultSet rs = prepStmt.executeQuery();
			
			while (rs.next()) {
				StatisticsData data = new StatisticsData();
				data.setPositionY(rs.getInt(1));
				data.setData(rs.getDate(2));
				
				lista.add(data);
			}
			return lista;
		} catch (SQLException e) {
			error(e.getErrorCode() + " : " + e.getMessage());
			e.printStackTrace();
		} catch (XPathExpressionException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			
			close();
		}

		return null;

	}
	


}
